using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

// Note: Here is useful link to see difference between MSSQL 2000 and MSSQL 2005 schema functions
// http://www.mssqltips.com/tip.asp?tip=1106

namespace Developex.GenerateClassOnShema.Classes
{
	public class DatabaseInfo
	{
		#region SQL

		private const string SELECT_TABLES_SQL = @"SELECT TABLE_CATALOG AS [Database], 
TABLE_SCHEMA AS Owner, 
TABLE_NAME AS Name, 
TABLE_TYPE FROM         
INFORMATION_SCHEMA.TABLES WHERE     
(TABLE_TYPE = 'BASE TABLE') AND 
(TABLE_NAME <> N'sysdiagrams')
  ";

		private const string SELECT_TABLE_SHEMA_SQL = @"
SELECT 
    TABLE_CATALOG AS [Database], 
    TABLE_SCHEMA AS Owner, 
    TABLE_NAME AS TableName, 
    COLUMN_NAME AS ColumnName,  
    ORDINAL_POSITION AS OrdinalPosition, 
    COLUMN_DEFAULT AS DefaultSetting, 
    IS_NULLABLE AS IsNullable, 
    DATA_TYPE AS DataType,  
    CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
    DATETIME_PRECISION AS DatePrecision,
    COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IsIdentity,
    COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsComputed') as IsComputed,
    (CASE WHEN PrimaryColumns.[ColumnName] IS NULL THEN 0 ELSE 1 END) as [IsPrimary]
FROM  INFORMATION_SCHEMA.COLUMNS [Columns]
LEFT JOIN 
(
  SELECT b.column_name as ColumnName
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a 
  INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b on b.constraint_name=a.constraint_name 
  WHERE a.constraint_type='PRIMARY KEY' and a.table_name=@t
) [PrimaryColumns] on [PrimaryColumns].[ColumnName] = [Columns].COLUMN_NAME
WHERE (TABLE_NAME = @t) ;";

		private const string SELECT_TABLE_CONSTRAINTS_SQL = @"
SELECT
KCU.COLUMN_NAME as [columnName], 
TC.CONSTRAINT_TYPE as ConstraintType,
so2.[name] as RefTable,
cs.[name] as RefColumn
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
JOIN  INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME 
JOIN sys.objects so1 ON so1.[name] = KCU.CONSTRAINT_NAME
LEFT OUTER JOIN sys.foreign_key_columns f1 on f1.constraint_object_id=so1.[object_id]
LEFT OUTER JOIN sys.objects so2 ON so2.[object_id] = f1.referenced_object_id
LEFT JOIN sys.columns cs ON cs.[object_id]=so2.[object_id] and cs.column_id=f1.referenced_column_id
WHERE KCU.TABLE_NAME=@t
";
		private const string SELECT_TABLE_CONSTRAINTS_SQL2000 = @"
SELECT 
KCU.COLUMN_NAME as [columnName], 
TC.CONSTRAINT_TYPE as ConstraintType,
so2.[name] as RefTable,
cs.[name] as RefColumn
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME 
JOIN sysobjects so1 ON so1.[name] = KCU.CONSTRAINT_NAME
LEFT OUTER JOIN sysforeignkeys f1 on f1.constid=so1.[id]
LEFT OUTER JOIN sysobjects so2 ON so2.[id] = f1.rkeyid
LEFT JOIN syscolumns cs ON cs.[id]=so2.[id] and cs.colid=f1.rkey
WHERE KCU.TABLE_NAME=@t
";

		#endregion

		#region Public functions

		public static List<string> GetTableList(SqlConnection conn)
		{
			List<string> result = new List<string>();
			using (SqlCommand command = new SqlCommand(SELECT_TABLES_SQL, conn))
			{
				SqlDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					string tablename = reader.GetString(2);
					result.Add(tablename);
				}
				reader.Close();
			}
			return result;
		}

		/// <summary>
		/// Read table data
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="tableName"></param>
		/// <param name="dbSettings"></param>
		/// <returns></returns>
		public static Table ReadTableData(SqlConnection conn, string tableName, DbSettings dbSettings)
		{
			TableSettings tableSettings = dbSettings.GetTableSettings(tableName);
			if (tableSettings == null || !tableSettings.TableSettingExists(TableSettingsName.InheritFrom))
				return ReadTableData_impl(conn, tableName, dbSettings);
			
			// InheritFrom

			string inheritTableName = tableSettings.GetTableSetting(TableSettingsName.InheritFrom);
			Table origTable = ReadTableData(conn, inheritTableName, dbSettings);
			Table viewTable = ReadTableData_impl(conn, tableName, dbSettings);
			if (origTable == null || viewTable == null)
				return null;

			Table resTable = new Table(tableName);
			foreach (Column column in viewTable.Columns)
			{
				resTable.Columns.Add(origTable.Contains(column.Name) ? origTable[column.Name] : column);
			}
			return resTable;
		}

		public static Table ReadTableData_impl(SqlConnection conn, string tableName, DbSettings dbSettings)
		{
			TableSettings tableSettings = dbSettings.GetTableSettings(tableName);
			// get list of columns to generate if defined
			List<string> columnsToGenerate = null;
			if (tableSettings != null && tableSettings.TableSettingExists(TableSettingsName.ColumnsToGenerate))
			{
				columnsToGenerate = new List<string>();
				string[] names = tableSettings.GetTableSetting(TableSettingsName.ColumnsToGenerate).Split(',');
				foreach (string name in names)
				{
					if (!string.IsNullOrEmpty(name))
						columnsToGenerate.Add(name.Trim().ToLower());
				}
			}

			Table tableObject = new Table(tableName);
			SqlCommand command = conn.CreateCommand();
			command.CommandText = SELECT_TABLE_SHEMA_SQL;
			command.Parameters.AddWithValue("@t", tableName);

			SqlDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				Column column = new Column();
				column.Name = (string)reader["ColumnName"];
				column.IsNullable = (string)reader["IsNullable"] == "YES";
				column.Type = GetType((string)reader["DataType"]);

				// check if we should skip this column
				if (columnsToGenerate != null && !columnsToGenerate.Contains(column.Name.ToLower()))
					continue;

				if (reader["DefaultSetting"] != DBNull.Value)
				{
					string defValue = (string)reader["DefaultSetting"];
					if (!string.IsNullOrEmpty(defValue))
					{
						defValue = defValue.Trim('(', ')');
						switch (column.Type)
						{
							case "string":
								column.DefValue = defValue.Remove(defValue.Length - 1, 1).Substring(defValue.IndexOf('\'') + 1);
								break;
							case "int":
								column.DefValue = int.Parse(defValue);
								break;
							case "bool":
								column.DefValue = int.Parse(defValue) == 1;
								break;
						}
					}
				}

				if (reader["MaxLength"] != DBNull.Value)
					column.MaxLength = (int)reader["MaxLength"];

				column.IsPrimaryKey = Convert.ToInt32(reader["IsPrimary"]) == 1;
				column.IsIdentity = Convert.ToInt32(reader["IsIdentity"]) == 1;
				column.IsComputed = Convert.ToInt32(reader["IsComputed"]) == 1;

				tableObject.Columns.Add(column);

			}
			reader.Close();


			command = conn.CreateCommand();
			// Note select appropriate SQL
			command.CommandText = dbSettings.IsSql2000 ? SELECT_TABLE_CONSTRAINTS_SQL2000 : SELECT_TABLE_CONSTRAINTS_SQL;
			command.Parameters.AddWithValue("@t", tableName);

			reader = command.ExecuteReader();
			while (reader.Read())
			{
				string columnName = reader.GetString(0);
				Column col = FindColumn(tableObject, columnName);
				if (col != null)
				{
					string type = reader.GetString(1);
					if (type == "FOREIGN KEY")
					{
						string ft = reader.GetString(2);
						if (dbSettings.ContainsTable(ft))
						{
							col.ForeignTable = ft;
						}
						else
						{
							foreach (TableSettings ts in dbSettings.GetAllTableSettings())
							{
								if (ts.TableSettingExists(TableSettingsName.InheritFrom))
								{
									string inh = ts.GetTableSetting(TableSettingsName.InheritFrom);
									if (string.Compare(inh, ft, true) == 0)
									{
										col.ForeignTable = ts.TableName;
										break;
									}
								}
							}
						}
						if (col.ForeignTable != null)
						{
							col.IsForeignKey = true;
						}
					}
				}
			}
			reader.Close();
			return tableObject;
		}

		#endregion

		#region Implementation

		private static string GetType(string dbtype)
		{
			switch (dbtype.ToLower())
			{
				case "bigint":
					return "long";
				case "tinyint":
				case "smallint":
				case "int":
				case "numeric":
					return "int";
				case "text":
				case "ntext":
				case "nchar":
				case "nvarchar":
				case "char":
				case "varchar":
					return "string";
				case "real":
					return "float";
				case "float":
					return "double";
				case "datetime":
				case "smalldatetime":
					return "DateTime";
				case "bit":
					return "bool";
				case "money":
				case "smallmoney":
					return "decimal";
				case "binary":
				case "varbinary":
				case "image":
					return "byte[]";
				case "uniqueidentifier":
					return "Guid";
				default:
					return dbtype;
			}
		}

		private static Column FindColumn(Table t, string columnName)
		{
			foreach (Column col in t.Columns)
			{
				if (col.Name.ToLower() == columnName.ToLower())
				{
					return col;
				}
			}
			return null;
		}

		#endregion
	}
}
